package struts.ext.returntype.handler;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Range;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.struts.action.ActionForward;

import struts.ext.commons.lang.reflect.ClassWrapper;
import struts.ext.commons.lang.reflect.Converts;
import struts.ext.util.Bean2ExcelRowConfig;
import struts.ext.util.BeanList2ExcelConfig;
import struts.ext.util.ExcelReturnWrapper;
import struts.ext.util.Strings;
import struts.ext.util.converter.AttrFormatter;

public class ExcelHandler implements Handler {

	//sheet.getSettings().setPassword("123"); //设置xls的密码 
	@Override
	public ActionForward handle(HttpServletRequest request,
			HttpServletResponse response, Object retValue) throws Exception {
		if(retValue instanceof BeanList2ExcelConfig){ //如果是返回值是BeanList2ExcelConfig
			BeanList2ExcelConfig config = (BeanList2ExcelConfig)retValue;
			if(config != null){
				
				response.setContentType("text/html;charset=utf-8");
				OutputStream os;
				try {
					//取得输出流   
					os = response.getOutputStream();
					
					response.reset();// 清空输出流   
					response.setHeader("Content-disposition", "attachment; filename=" + encode(Strings.nvl(config.getTitle(), "data")) + ".xls");// 设定输出文件头   
					response.setContentType("application/msexcel");// 定义输出类型 
					
					processExcel(os, config);//生成excel到流中
				} catch (IOException e) {
					e.printStackTrace();
					System.out.println("从response中获得输出流失败！");
				} 
			}
		}else{
			throw new Exception("输出Excel表格的action请返回" + ExcelReturnWrapper.class.getName() + "类型。");
		}

		return null;
	}
	
	/**
	 * 加上编码，去除中文乱码
	 * @param nvl
	 * @return
	 */
	private String encode(String nvl) {
		try {
			return new String(nvl.getBytes(),"iso-8859-1");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
			return nvl;
		}
	}


	public void processExcel(OutputStream os, BeanList2ExcelConfig config) throws Exception{
		
		if(config.getList() == null && config.getList().size() < 1){
			throw new Exception("要生成excel的数据为空。");
		}
		if(config.getMap() == null || config.getMap().size() < 1 ){
			throw new Exception("生成excel的配置文件为空。");
		}
		
		Map<String,Bean2ExcelRowConfig> map = config.getMap(); //得到要处理的数据
		
		//得到javabean的属性名和Excel中对应的表头的映射关系
		List<String> attrs = new LinkedList<String>(); //属性名
		List<Bean2ExcelRowConfig> rowConfigs = new LinkedList<Bean2ExcelRowConfig>(); //属性的配置
		
		for(Map.Entry<String, Bean2ExcelRowConfig> rConfig : map.entrySet()){
			attrs.add(rConfig.getKey()); //获得属性名集合
			rowConfigs.add(rConfig.getValue()); //得到每个属性的配置
		}		

        WritableWorkbook wbook ;
		try {
			wbook = Workbook.createWorkbook(os);
		} catch (IOException e2) {
			System.out.println("创建Workbook失败");
			e2.printStackTrace();
			return;
		} 
		
		// 建立excel文件   
        String sheetName = Strings.nvl(config.getSheetName(), "表格");
        WritableSheet wsheet = wbook.createSheet(sheetName, 0); //创建sheet
        
        //配置每列的宽度
        configColumnWidth(wsheet,rowConfigs);
       
		// 设置excel标题    第一行
		int labelIndex = 0;
		wsheet.addCell(new Label(0,0,Strings.nvl(config.getTitle(),""),getTitleCellFormat()));
		wsheet.mergeCells(0,0,attrs.size()-1,0);//（起始列，起始行，终止列，终止行，0-based）;
		 
		//生成列名，label
		for(int i = 0; i < rowConfigs.size(); i++){
			Bean2ExcelRowConfig rowCfg = rowConfigs.get(i);
			String labelName = Strings.nvl(rowCfg.getLabel(),"notitle");
			
			try {
				//从第二行开始
				wsheet.addCell(new Label(labelIndex++, 1, labelName, getLabelCellFormat()));
			} catch (RowsExceededException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
				continue;
			}   
		}
		
		
		// 开始生成主体内容                   
		int rIndex = 2;//行号从第3行开始
		for(Object obj : config.getList()) {   
			int cIndex = 0; //列号，从第一列开始
			for(int i = 0; i < attrs.size(); i++ ){
				ClassWrapper<?> classWrapper = ClassWrapper.wrap(obj);
				try {
					Object value = classWrapper.getValueByDot(obj, attrs.get(i));
					String formattedValue = ""; //经过格式化后的数据
 					try{
						AttrFormatter formatter = rowConfigs.get(i).getConverter();
						formattedValue = formatter.format(value);
					}catch(Exception e){
						//如果没使用自定义的转换器，或者自定义的转换器转换失败就使用Converts.convert(value,String.class)方法来转换。
						formattedValue = Converts.convert(value,String.class);
					}
					wsheet.addCell(new Label(cIndex++,rIndex,formattedValue));
				} catch (RowsExceededException e) {
					e.printStackTrace();
					//行数太多
				} catch (WriteException e) {
					e.printStackTrace();
					continue;
				}
			}
			rIndex++;
		}           
		
		// 主体内容生成结束           
		try {
			// 写入文件   
			wbook.write();
			wbook.close();  
			os.close(); // 关闭流
		}catch (WriteException e) {
			e.printStackTrace();
		}catch (IOException e) {
			e.printStackTrace();
		}
		
	}

	private CellFormat getLabelCellFormat() {
		WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 13, WritableFont.BOLD, 
                false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   

		WritableCellFormat cellFormat = new WritableCellFormat(font);
		return cellFormat;
	}

	private CellFormat getTitleCellFormat() {
		WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 16, WritableFont.BOLD, 
                false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   

		WritableCellFormat titleCellFormat = new WritableCellFormat(titleFont); 
		try {
			titleCellFormat.setAlignment(Alignment.CENTRE);
		} catch (WriteException e) {
			e.printStackTrace();
		}
		return titleCellFormat;
	}

	/**
	 * 为Sheet设置列宽
	 * @param wsheet
	 * @param rowConfigs
	 */
	private void configColumnWidth(WritableSheet wsheet,
			List<Bean2ExcelRowConfig> rowConfigs) {
		if(rowConfigs != null){
			for(int i = 0; i < rowConfigs.size(); i++){
				wsheet.setColumnView(i, rowConfigs.get(i).getWidth());
			}
		}
	}

}
